﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;



namespace Catering.View_Operaction
{
    public class View_Operaction_Excel
    {
        #region 电子表格填充到数据集
        /// <summary>
        /// 将电子表格中的数据通过查询方式填充到数据集中
        /// </summary>
        /// <param name="path">将要填充的Excel文件路径</param>
        /// <returns></returns>
        public static DataSet exceltodataset(string path)
        {
            string connstr = null;
            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataSet ds = new DataSet();
            string sql = null;
            try
            {
                connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties='Excel 8.0;IMEX＝1;HDR=yes'";
                conn = new OleDbConnection(connstr);
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                {
                    dt.Rows[0]["TABLE_Name"] += "$";
                }
                sql = "SELECT * FROM [" + dt.Rows[0]["TABLE_Name"].ToString() + "] ";
                da = new OleDbDataAdapter(sql, connstr);
                ds.Clear();
                da.Fill(ds);
                conn.Close();
            }
            catch
            {
                connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;IMEX＝1;HDR=yes'";
                conn = new OleDbConnection(connstr);
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                {
                    dt.Rows[0]["TABLE_Name"] += "$";
                }
                sql = "SELECT * FROM [" + dt.Rows[0]["TABLE_Name"].ToString() + "] ";
                da = new OleDbDataAdapter(sql, connstr);
                da.Fill(ds);
                conn.Close();
            }
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                Microsoft.Office.Interop.Excel.Application excel1 = null;
                Excel.Workbook workbook = null;
                Microsoft.Office.Interop.Excel._Worksheet ws = null;
                Excel.Range Range = null;
                try
                {
                    excel1 = new Microsoft.Office.Interop.Excel.Application();
                    //excel1.Workbooks.Add(path);//引用Excel工作簿
                    workbook = excel1.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
                    ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                    ws = (Microsoft.Office.Interop.Excel._Worksheet)excel1.ActiveSheet;//获取当前活动工作薄
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string phid = ds.Tables[0].Rows[i][0].ToString();
                        string piaodate = ds.Tables[0].Rows[i][2].ToString();
                        if (string.IsNullOrEmpty(phid) && ds.Tables[0].Rows[i][1].ToString() != "")
                        {
                            Range = ws.Cells[i + 2, 1] as Microsoft.Office.Interop.Excel.Range;
                            if (Range != null)
                            {
                                string strlist = Range.Value.ToString();
                                ds.Tables[0].Rows[i][0] = strlist;
                            }
                        }
                        if (string.IsNullOrEmpty(piaodate) && ds.Tables[0].Rows[i][1].ToString() != "")
                        {
                            Range = ws.Cells[i + 2, 3] as Microsoft.Office.Interop.Excel.Range;
                            if (Range != null)
                            {
                                string strlist = Range.Value.ToString();
                                ds.Tables[0].Rows[i][2] = strlist;
                            }
                        }
                    }
                }
                finally
                {
                    if (excel1 != null)
                    {
                        if (workbook != null)
                        {
                            if (ws != null)
                            {
                                if (Range != null)
                                {

                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);
                                    Range = null;

                                }
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                                ws = null;
                            }
                            workbook.Close(false, null, null);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                            workbook = null;
                        }
                        excel1.Application.Workbooks.Close();
                        excel1.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
                        Toolss.KillExcel(excel1);
                        excel1 = null;
                        GC.Collect();
                    }
                }
            }
            return ds;
        }

        public static DataSet exceltodataset(string path, List<int> inspection, int notnullindex)
        {
            string connstr = null;
            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataSet ds = new DataSet();
            string sql = null;
            try
            {
                connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties='Excel 8.0;IMEX＝1;HDR=yes'";
                conn = new OleDbConnection(connstr);
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                {
                    dt.Rows[0]["TABLE_Name"] += "$";
                }
                sql = "SELECT * FROM [" + dt.Rows[0]["TABLE_Name"].ToString() + "] ";
                da = new OleDbDataAdapter(sql, connstr);
                ds.Clear();
                da.Fill(ds);
                conn.Close();
            }
            catch
            {
                connstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;IMEX＝1;HDR=yes'";
                conn = new OleDbConnection(connstr);
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                {
                    dt.Rows[0]["TABLE_Name"] += "$";
                }
                sql = "SELECT * FROM [" + dt.Rows[0]["TABLE_Name"].ToString() + "] ";
                da = new OleDbDataAdapter(sql, connstr);
                da.Fill(ds);
                conn.Close();
            }
            if (inspection != null && inspection.Count > 0)
            {
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    Microsoft.Office.Interop.Excel.Application excel1 = null;
                    Excel.Workbook workbook = null;
                    Microsoft.Office.Interop.Excel._Worksheet ws = null;
                    Excel.Range Range = null;
                    try
                    {
                        excel1 = new Microsoft.Office.Interop.Excel.Application();
                        //excel1.Workbooks.Add(path);//引用Excel工作簿
                        workbook = excel1.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
                        ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                        ws = (Microsoft.Office.Interop.Excel._Worksheet)excel1.ActiveSheet;//获取当前活动工作薄
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            for (int j = 0; j < inspection.Count; j++)
                            {
                                string value = ds.Tables[0].Rows[i][inspection[j]].ToString();
                                if (string.IsNullOrEmpty(value) && ds.Tables[0].Rows[i][notnullindex].ToString() != "")
                                {
                                    Range = ws.Cells[i + 2, (inspection[j] + 1)] as Microsoft.Office.Interop.Excel.Range;
                                    if (Range != null)
                                    {
                                        string strlist = "0";
                                        try
                                        {
                                            strlist = Range.Value.ToString();
                                        }
                                        catch { }
                                        ds.Tables[0].Rows[i][inspection[j]] = strlist;
                                    }
                                }
                            }
                        }
                    }
                    finally
                    {
                        if (excel1 != null)
                        {
                            if (workbook != null)
                            {
                                if (ws != null)
                                {
                                    if (Range != null)
                                    {

                                        System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);
                                        Range = null;

                                    }
                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                                    ws = null;
                                }
                                workbook.Close(false, null, null);
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                                workbook = null;
                            }
                            excel1.Application.Workbooks.Close();
                            excel1.Quit();
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
                            Toolss.KillExcel(excel1);
                            excel1 = null;
                            GC.Collect();
                        }
                    }
                }
            }
            return ds;
        }

        #endregion

        /// <summary>
        /// 获得模板以及临时文件路径
        /// </summary>
        /// <param name="ExcelFileName">模板文件路径</param>
        /// <param name="DownloadFileName">下载文件路径</param>
        /// <param name="StrTemplatesPath">返回模板全路径</param>
        /// <param name="StrTempPath">返回下载文件的全路径</param>
        private static void GetExcelPath(string ExcelFileName, string DownloadFileName, out string StrTemplatesPath, out string StrTempPath)
        {

            //模板的路径
            StrTemplatesPath = "../../" + Catering_DBOpertion.PubConstant.GetConfigValue("TempExcel") + "/" + ExcelFileName + ".xls";
            //临时文件路径
            StrTempPath = "../../" + Catering_DBOpertion.PubConstant.GetConfigValue("UpExcel") + "/" + DownloadFileName + ".xls";
        }

        /// <summary>
        /// 导出Excel到客户端，报表中既有表格样式也有单个值。
        /// </summary>
        /// <param name="ExcelFileName">模板的名称</param>
        /// <param name="DownloadFileName">下载后的名称</param>
        /// <param name="dt">需要导出数据的数据集，DataTable</param>
        /// <param name="startRowIndex">导出报表中的数据占位开始点的行号，从1开始</param>
        /// <param name="startCellIndex">导出报表中的数据占位开始点的列号，从1开始</param>
        /// <param name="ht">报表中需要输入的单个数据属性，key值为：B:2,行号和列号中间用半角冒号分割</param>
        public void ExportExl(string strExcelFileName, string strDownloadFileName, System.Data.DataTable dt, int intStartRowIndex, int intStartCellIndex, Hashtable ht)
        {
            //excel模板路径
            string strTemplatesPath;
            string strTempPath;
            if (dt != null && ht != null)
            {
                if (dt.Rows.Count > 0 && ht.Count > 0)
                {

                    GetExcelPath(strExcelFileName, strDownloadFileName, out strTemplatesPath, out strTempPath);
                    //得到操作 excel程序集合
                    Excel.ApplicationClass excel = new Excel.ApplicationClass();
                    Excel.Workbooks workbooks;

                    //Excel.Workbook workbook;
                    _Workbook workbook;
                    //Excel.Worksheet worksheet;
                    _Worksheet worksheet;
                    Excel.Sheets worksheets;

                    excel = new Excel.ApplicationClass();
                    workbooks = excel.Workbooks;
                    //模板本地路径
                    string str = System.Web.HttpContext.Current.Server.MapPath(strTemplatesPath);
                    //模板文件 
                    workbook = workbooks.Add(str);
                    worksheets = (Excel.Sheets)excel.Worksheets;
                    worksheet = (Excel.Worksheet)worksheets.get_Item(1);
                    //必须写，用来触发数据的焦点。
                    worksheet.Activate();
                    //将集合里的数据添加到文件里
                    foreach (System.Collections.DictionaryEntry objDE in ht)
                    {
                        string[] Pint = objDE.Key.ToString().Split(new char[] { ':', ' ' });
                        excel.Cells[Pint[1], Pint[0]] = objDE.Value.ToString().Trim();

                    }

                    //将数据据表转换成2维数组
                    string[,] StringArray = ConvertDataTableToStringArray(dt);
                    //得到一个区域
                    Excel.Range StringRange = (Excel.Range)worksheet.Cells[intStartRowIndex, intStartCellIndex];
                    //得到区域范围
                    StringRange = StringRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
                    //将数组值给范围，写入电子表格
                    StringRange.Value2 = StringArray;
                    StringArray = null;
                    excel.Visible = false;
                    // 准备打开Excel文件时的缺省参数对象
                    object oMissing = System.Reflection.Missing.Value;

                    string path = System.Web.HttpContext.Current.Server.MapPath(strTempPath);

                    FileDetele(path);
                    workbook.SaveCopyAs(path);
                    //关闭资源           
                    ExcelClose(excel, workbooks, workbook, worksheet);
                    //下载文件
                    DownloadExcel(strTempPath);
                }
            }

        }

        private static void FileDetele(string strPath)
        {
            //string strDPath = strPath.Substring(0, strPath.LastIndexOf("\\") + 1);
            //DirectoryInfo dirinfo = new DirectoryInfo(strDPath);

            //dirinfo.Attributes = FileAttributes.Normal;
            //DirectorySecurity dirsecurity = dirinfo.GetAccessControl();

            //dirsecurity.AddAccessRule(new FileSystemAccessRule("NETWORK", FileSystemRights.FullControl, InheritanceFlags.ObjectInherit, PropagationFlags.InheritOnly, AccessControlType.Allow));

            //dirinfo.SetAccessControl(dirsecurity);

            if (File.Exists(strPath))
            {
                File.Delete(strPath);
            }
        }
        /// <summary>
        /// 关闭资源
        /// </summary>
        /// <param name="excel">Excel对象</param>
        /// <param name="workbooks">workbook对象集</param>
        /// <param name="workbook">workbook对象</param>
        /// <param name="worksheet">worksheet对象</param>
        private static void ExcelClose(Excel.ApplicationClass excel, Excel.Workbooks workbooks, _Workbook workbook, _Worksheet worksheet)
        {
            //关闭
            workbook.Close(false, null, null);
            workbooks.Close();
            //退出Excel
            excel.Quit();
            excel.Visible = false;
            //关闭进程
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //垃圾回收
            GC.Collect();
        }
        /// <summary>
        /// 文件下载
        /// </summary>
        /// <param name="ExcelFileName">下载文件名</param>
        public static void DownloadExcel(string strExcelFileName)
        {
            string path = System.Web.HttpContext.Current.Server.MapPath(strExcelFileName);
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(strExcelFileName));
            // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
            System.Web.HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());

            // 指定返回的是一个不能被客户端读取的流，必须被下载 
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            // 把文件流发送到客户端 
            System.Web.HttpContext.Current.Response.WriteFile(file.FullName);
            // 停止页面的执行 

            System.Web.HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 将DATATABLE转换成二维数组
        /// </summary>
        /// <param name="dt">要转换的DATATABLE</param>
        /// <returns>返回相应长度的二维数组</returns>
        private string[,] ConvertDataTableToStringArray(System.Data.DataTable dt)
        {
            string[,] returnArray = new string[dt.Rows.Count, dt.Columns.Count];
            int i = 0, j = 0;
            //使用嵌套循环将数据表中的数据转换到二维数组中
            foreach (System.Data.DataRow row in dt.Rows)
            {
                j = 0;
                foreach (System.Data.DataColumn column in dt.Columns)
                {
                    returnArray[i, j] = row[column.ColumnName].ToString().Trim();
                    j++;
                }
                i++;
            }
            return returnArray;
        }

        /// <summary>
        /// 库存台账    导出Excel到客户端，报表中既有表格样式也有单个值。
        /// </summary>
        /// <param name="ExcelFileName">模板的名称</param>
        /// <param name="DownloadFileName">下载后的名称</param>
        /// <param name="dt">需要导出数据的数据集，DataTable</param>
        public static void KctzExcel(string strExcelFileName, string strDownloadFileName, System.Data.DataTable dt)
        {
            //excel模板路径
            string strTemplatesPath;
            string strTempPath;
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {

                    GetExcelPath(strExcelFileName, strDownloadFileName, out strTemplatesPath, out strTempPath);
                    //得到操作 excel程序集合
                    ApplicationClass excel = new ApplicationClass();
                    Workbooks workbooks;

                    //Excel.Workbook workbook;
                    _Workbook workbook;
                    //Excel.Worksheet worksheet;
                    _Worksheet worksheet;
                    Sheets worksheets;

                    excel = new ApplicationClass();
                    workbooks = excel.Workbooks;
                    //模板本地路径
                    string str = System.Web.HttpContext.Current.Server.MapPath(strTemplatesPath);
                    //模板文件 
                    workbook = workbooks.Add(str);
                    worksheets = (Sheets)excel.Worksheets;
                    worksheet = (Worksheet)worksheets.get_Item(1);
                    //必须写，用来触发数据的焦点。

                    worksheet.Activate();

                    worksheet.Cells[2, "A"] = "名称 " + dt.Rows[0]["mingcheng"].ToString();

                    worksheet.Cells[3, "D"] = "名称 " + dt.Rows[0]["mingcheng"].ToString();
                    int hang = 6;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        var riqi = DateTime.Parse(dt.Rows[i]["riqi"].ToString());
                        worksheet.Cells[hang, "A"] = riqi.Month;
                        worksheet.Cells[hang, "B"] = riqi.Day;
                        worksheet.Cells[hang, "C"] = dt.Rows[i]["danhao"];
                        worksheet.Cells[hang, "D"] = dt.Rows[i]["mingcheng"];
                        worksheet.Cells[hang, "E"] = dt.Rows[i]["ruku"];
                        worksheet.Cells[hang, "F"] = dt.Rows[i]["chuku"];
                        worksheet.Cells[hang, "G"] = dt.Rows[i]["jiecun"];
                        worksheet.Cells[hang, "H"] = dt.Rows[i]["jiacharen"];
                        hang = hang + 1;
                    }
                    object oMissing = System.Reflection.Missing.Value;

                    string path = System.Web.HttpContext.Current.Server.MapPath(strTempPath);


                    FileDetele(path);
                    workbook.SaveCopyAs(path);
                    //关闭资源           
                    ExcelClose(excel, workbooks, workbook, worksheet);
                    //下载文件
                    DownloadExcel(strTempPath);
                }
            }

        }
    }
}